IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[State]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[State]
(
id int NOT NULL PRIMARY KEY  IDENTITY(1,1),
name varchar(50),
numbPopulation int,
numDistricts int
); 
END

/*--------------------------------------------------------------*/

/*District*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[District]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[District]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
name varchar(255),
numbPopulation int,
stateId int,
FOREIGN KEY (stateId) REFERENCES [dbo].[State](id) 
); 
END

/*--------------------------------------------------------------*/

/*User*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[User]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
name varchar(255),
userName varchar(255),
userPassword varchar(255),
DistrictId int ,
FOREIGN KEY (DistrictId) REFERENCES District(id) 
); 
END

/*--------------------------------------------------------------*/

/*UserType*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[UserType]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[UserType]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
name varchar(255),

); 
END

/*--------------------------------------------------------------*/

/*Representitve*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Representitve]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Representitve]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
userID int,
DistrictID int,
FOREIGN KEY (DistrictId) REFERENCES District(id),
FOREIGN KEY (userID) REFERENCES [dbo].[User](id),
 
); 
END

/*--------------------------------------------------------------*/

/*Senator*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Senator]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Senator]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
userID int,
stateID int,
FOREIGN KEY (stateId) REFERENCES state(id),
FOREIGN KEY (userID) REFERENCES [dbo].[User](id),
 
); 
END


/*--------------------------------------------------------------*/

/*Subject*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Subject]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Subject]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
title varchar(255),
description varchar(225),
minVot int,
maxVot int,
status varchar(255),
voteNumb int  
); 
END

/*--------------------------------------------------------------*/

/*SubjectAuthor*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[SubjectAuthor]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[SubjectAuthor]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
subjectID int,
UserID int,
FOREIGN KEY (subjectID) REFERENCES subject(id),
FOREIGN KEY (userID) REFERENCES [dbo].[User](id),
 
); 
END

/*--------------------------------------------------------------*/

/*Party*/
IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Party]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[Party]
(
id int NOT NULL PRIMARY KEY IDENTITY(1,1),
name varchar(225),
senatorNumb int,
representitiveNumb int,
 
); 
END



